<?php
  
  //Global variables - access these using $GLOBALS['db'], for example
  $db = new PDO('sqlite:supermaps.db');
    
  //Database querying
  
  function getList($list_id) {
    return make_query("select * from Lists where id = $list_id")->fetch(PDO::FETCH_ASSOC);
  }
  
  function getListLocations($list_id) {
    $list = getList($list_id);
    $locations = array();
    
    $loc_tok = strtok($list[locations], ","); //"Location Tokenizer" i.e. loc_tok. $list[locations] will be something like "1,2,3,4" where each number is a Location id
    while ($loc_tok !== false) { //$loc_tok is presumably a single number that corresponds to a valid Location id
      $location = make_query('select * from Locations where id='.$loc_tok)->fetch(PDO::FETCH_ASSOC); //Obtaining the array containing the Location's information
      array_push($locations, $location);
      $loc_tok = strtok(",");
    }
    return $locations;
  }
  
  function getCategories() {
    $categories_array = make_query("select category from Lists group by category order by category asc")->fetchAll(PDO::FETCH_ASSOC);
    $categories = array();
    foreach($categories_array as $category) {
      array_push($categories, $category[category]);
    }
    return $categories;
  }
  
  //Helpers
  
  function get_last_id($table) {
    $tuple = $GLOBALS['db']->query('select * from '.$table.' order by id desc limit 1')->fetch(PDO::FETCH_ASSOC);
    if ($tuple[id] !== NULL) return $tuple[id];
    else return 0;
  }
  
  function get_new_id($table) {
    return get_last_id($table) + 1;
  }
  
  /* make_query
   * ----------
   * General purpose function for executing any database query. 
   * The return type can either be an array or an array of arrays.
   */
  function make_query($statement) {
    return $GLOBALS['db']->query($statement);
  }

  function exec_statement($statement) {
    //printline("exec_statement: $statement");
    return $GLOBALS['db']->exec($statement);
  }
  
  function insertHelper($tablename, $values) {
    return "insert into $tablename values (".get_new_id($tablename).", $values)";
  }
  //Database manipulation
  
  function createNewUser($name, $password, $lists = '') {
    $statement = insertHelper('Users', "'$name', '$password', '$lists'");
    exec_statement($statement);
  }
  
  function createNewList($name, $category = '', $locations = '') {
    $statement = insertHelper('Lists', "'$name', '$category', '$locations'");
    exec_statement($statement);
  }
  
  function createNewLocation($name, $building, $category = '', $open = 'NULL', $close = 'NULL') {
    $statement = insertHelper('Locations', "'$name', $building, '$category', '$open', '$close'");
    exec_statement($statement);
  }
  
  function createNewBuilding($name, $x = 0.0, $y = 0.0, $parent_map = '', $internal_map = 'NULL') {
    $statement = insertHelper('Buildings', "'$name', $internal_map, $x, $y, '$parent_map'");
    exec_statement($statement);
  }
  
  function createNewInternalMap($building, $pages) {
    exec_statement(insertHelper('InternalMaps', "$building, '$pages'"));
  }
  
  function createNewPerson($name, $room = 'NULL', $building) {
    $statement = insertHelper('People', "'$name', '$room', $building");
    exec_statement($statement);
  }
  
  function createNewPage($internal_map_id, $page_order, $filename) {
    exec_statement(insertHelper('Pages', "$internal_map_id, $page_order, '$filename'"));
  }
  
  
  
  
  
  
  
  //Debugging functions
  
  function printline($string) {
    echo($string."<br>");
  }
?>